﻿using aier.dataaccess.model;
using Dapper;
using log4net;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace aier.dataaccess.dapper
{
    public class RecommendDal
    {
        public DapperHelper dapperHelper { get; set; }

        public ILog log { get; set; }

        /// <summary>
        /// 查询推荐记录
        /// 查找条件：推荐记录ID
        /// 原程序 RecommendServiceImpl/qryRecommend
        /// </summary>
        /// <param name="recommendid">推荐记录ID</param>
        /// <returns></returns>
        public RecommendView QueryRecommendById(Guid recommendid)
        {
            RecommendView recommendView = null;
            SqlBuilder sqlBuilder = new SqlBuilder();
            DynamicParameters sqlParams = new DynamicParameters();

            dapperHelper.ExecueWithoutTransaction(a =>
            {
                string sql = @"select * from AIER_RECOMMEND_INFO s /**where**/ ";
                var sqlTemplate = sqlBuilder.AddTemplate(sql);

                if (recommendid != Guid.Empty)
                {
                    sqlBuilder.Where(" s.new_recommendId=@new_recommendId ");
                    sqlParams.Add("@new_recommendId", recommendid);
                }              
                recommendView = a.QueryFirstOrDefault<RecommendView>(sqlTemplate.RawSql, sqlParams);
            });

            return recommendView;
        }
        /// <summary>
        /// 查询推荐记录
        /// 查找条件：contactid,ownerid
        /// 原程序 RecommendServiceImpl/qryRecommendList
        /// </summary>
        /// <param name="contactid"></param>
        /// <param name="ownerid"></param>
        /// <returns></returns>
        public List<RecommendView> QueryRecommendList(Guid contactid,Guid ownerid)
        {
            List<RecommendView> recommendViews = null;
            SqlBuilder sqlBuilder = new SqlBuilder();
            DynamicParameters sqlParams = new DynamicParameters();

            dapperHelper.ExecueWithoutTransaction(a =>
            {
                string sql = @"select * from AIER_RECOMMEND_INFO s /**where**/ ";
                var sqlTemplate = sqlBuilder.AddTemplate(sql);

                if (contactid != Guid.Empty)
                {
                    sqlBuilder.Where(" s.new_contact=@new_contact ");
                    sqlParams.Add("@new_contact", contactid);
                }
                if (ownerid != Guid.Empty)
                {
                    sqlBuilder.Where(" s.OwnerId=@OwnerId ");
                    sqlParams.Add("@OwnerId", ownerid);
                }
                sqlBuilder.OrderBy("new_appointmenttothehospitaldate desc");
                log.Info(string.Format("RecommendDal对像中,QueryRecommendList ,SQL：'{0}' 参数：{1}", sqlTemplate.RawSql, sqlParams.ParametersToString(sqlTemplate.RawSql)));
                recommendViews = a.Query<RecommendView>(sqlTemplate.RawSql, sqlParams).ToList();
            });

            return recommendViews   ;
        }
        /// <summary>
        /// 查询推荐记录
        /// 查找条件：startDateStr,endDateStr,istohospital,ownerid
        /// 原程序 RecommendServiceImpl/qryRecommendList
        /// </summary>
        /// <param name="startDateStr"></param>
        /// <param name="endDateStr"></param>
        /// <param name="istohospital"></param>
        /// <param name="ownerid"></param>
        /// <returns></returns>
        public List<RecommendView> QueryRecommendListByConditions(string startDateStr, string endDateStr,string hospital , Guid ownerid,Guid contactid, bool?istohospital=null)
        {
            List<RecommendView> recommendViews = null;
            SqlBuilder sqlBuilder = new SqlBuilder();
            DynamicParameters sqlParams = new DynamicParameters();

            dapperHelper.ExecueWithoutTransaction(a =>
            {
                string sql = @"select new_recommendId,new_leadName,new_appointmenttothehospitaldate,replace(new_hospitalName,'爱尔眼科','') as new_hospitalName,new_networkhospitalwherethehospitalName,new_istohospital from AIER_RECOMMEND_INFO2 s /**where**/ ";
                var sqlTemplate = sqlBuilder.AddTemplate(sql);

                if (!string.IsNullOrWhiteSpace(startDateStr))
                {
                    string sqll = "s.new_appointmenttothehospitaldate>='" + startDateStr + "'";
                    sqlBuilder.Where(sqll);
                }
                if (!string.IsNullOrWhiteSpace(endDateStr))
                {
                    string sqll = "s.new_appointmenttothehospitaldate<='" + endDateStr + "'";
                    sqlBuilder.Where(sqll);
                }
                if (istohospital != null)
                {
                    sqlBuilder.Where(" s.new_istohospital=@new_istohospital ");
                    sqlParams.Add("@new_istohospital", istohospital);
                }
                if (ownerid != Guid.Empty)
                {
                    sqlBuilder.Where(" s.OwnerId=@OwnerId ");
                    sqlParams.Add("@OwnerId", ownerid);
                }

                if (contactid != Guid.Empty)
                {
                    sqlBuilder.Where(" s.new_contact=@new_contact ");
                    sqlParams.Add("@new_contact", contactid);
                }
                sqlBuilder.OrderBy("new_appointmenttothehospitaldate desc");
                //log.Info(string.Format("RecommendDal对像中,QueryRecommendListByConditions ,SQL：'{0}' 参数：{1}", sqlTemplate.RawSql, sqlParams.ParametersToString(sqlTemplate.RawSql)));
                recommendViews = a.Query<RecommendView>(sqlTemplate.RawSql, sqlParams).ToList();
            });

            return recommendViews;
        }
    }
}
